## **********************************************
##           China Map Preprocessing       
##            Hao Zhang & Ye Zhang
##                 2025/7/24
## **********************************************

# load packages
library(readstata13)
library(tidyverse)
library(haven)
library(doBy)

# set working directory
setwd("../../raw data")

# read in data
options(scipen = 100)
firm <- read.dta13("cies 1998-2007.dta")
firm <- firm %>% mutate(city = as.numeric(substr(as.character(city), 0, 4)))

# create total_insurance and rate
firm_base <- firm %>% select(city, year, frdm, wage) %>%
  filter(wage > 0) %>% mutate(year = year + 1)
firm <- firm %>% filter(insurance >= 0)

# across-year analysis
firm <- firm %>% mutate(insurance_dummy = ifelse(insurance == 0, 0, 1), 
                        medical_dummy = ifelse(medical == 0, 0, 1))

# pick year
firm2007 <- firm %>% filter(year == 2007)
result <- summaryBy(insurance_dummy + medical_dummy ~ city, data = firm2007, FUN = mean)
colnames(result) <- c("citycode", "unemployment", "medical")
setwd("../main text/Figure 1")
write_dta(result, "result.dta")
